/**
 * FileName: Home
 * Author:   秋水浮萍
 * Date:     2020/6/22 0:43
 * Description:
 * History:
 * <author>          <time>          <version>          <desc>
 * 作者姓名           修改时间           版本号              描述
 */
package com.crm.crm.mapper;

import com.crm.entity.MyHome;
import com.crm.entity.SysUser;
import com.crm.model.Aim;
import com.crm.model.AimExcel;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * 〈一句话功能简述〉<br>
 * 〈〉
 *
 * @author 秋水浮萍
 * @create 2020/6/22
 * @since 1.0.0
 */

@Mapper
public interface SysHomeMapper {

    @Select("SELECT crm_partner_type.partner_name as name,count(*) as sum \n" +
            "            ,(SELECT COUNT(partner_type) FROM crm_partner where disabled = 0 and Date_SUB(curdate(),INTERVAL 30 DAY )<=create_time and create_time <CURDATE() and partner_type =#{type}) as count FROM \n" +
            "            crm_partner inner join crm_partner_type on \n" +
            "            crm_partner .partner_type=crm_partner_type.partner_type \n" +
            "            where crm_partner.partner_type=#{type} and crm_partner.disabled=0 ")
    MyHome homeList(int type);



    /**
     * 添加目标数据
     *
     * @param aim
     * @return
     */
    @Insert("<script>" +
            "INSERT INTO crm_user_aim" +
            "<trim prefix='(' suffix=')' suffixOverrides=','>" +
            "<if test='quarter1 != null'>" +
            "quarter1," +
            "</if>" +
            "<if test='quarter2 != null'>" +
            "quarter2," +
            "</if>" +
            "<if test='quarter3 != null '>" +
            "quarter3," +
            "</if>" +
            "<if test='quarter4 != null '>" +
            "quarter4," +
            "</if>" +
            "<if test='yr != null'>" +
            "yr," +
            "</if>" +
            "<if test='userId != null '>" +
            "user_id," +
            "</if>" +
            "<if test='createTime != null'>" +
            "create_time," +
            "</if>" +
            "<if test='year != null'>" +
            "year," +
            "</if>" +
            "<if test='updateTime != null'>" +
            "update_time," +
            "</if>" +
            "<if test='depId != null'>" +
            "dep_id" +
            "</if>" +
            "</trim>" +
            "<trim prefix='values (' suffix=')' suffixOverrides=','>" +
            "<if test='quarter1 != null '>" +
            "#{quarter1}," +
            "</if>" +
            "<if test='quarter2 != null '>" +
            "#{quarter2}," +
            "</if>" +
            "<if test='quarter3 != null '>" +
            "#{quarter3}," +
            "</if>" +
            "<if test='quarter4 != null'>" +
            "#{quarter4}," +
            "</if>" +
            "<if test='yr != null '>" +
            "#{yr}," +
            "</if>" +
            "<if test='userId != null '>" +
            "#{userId}," +
            "</if>" +
            "<if test='createTime != null '>" +
            "#{createTime}," +
            "</if>" +
            "<if test='year != null '>" +
            "#{year}," +
            "</if>" +
            "<if test='updateTime != null '>" +
            "#{updateTime}," +
            "</if>" +
            "<if test='depId != null'>" +
            "#{depId}" +
            "</if>" +
            "</trim>" +
            "</script>")
    Integer Aim(Aim aim);

    /**
     * 查询年份目标数据
     *
     * @param userId
     * @return
     */
    @Select("select * from crm_user_aim where user_id=#{userId} and year=#{date}")
    Aim findAim(String userId,String date);

    /**
     * 更新目标
     *
     * @return
     */
    @Update("<script>" +
            "update crm_user_aim " +
            " <trim prefix=' set ' suffixOverrides=' , '>" +
            "<if test=' quarter1 != null'>" +
            " quarter1=#{quarter1}," +
            "</if>" +
            "<if test='quarter2 != null'>" +
            "quarter2=#{quarter2}," +
            "</if>" +
            "<if test='quarter3 != null '>" +
            "quarter3=#{quarter3}," +
            "</if>" +
            "<if test='quarter4 != null '>" +
            "quarter4=#{quarter4}," +
            "</if>" +
            "<if test='yr != null'>" +
            "yr=#{yr}," +
            "</if>" +
            "<if test='updateTime != null'>" +
            "update_time=#{updateTime}," +
            "</if>" +
            "<if test='depId != null'>" +
            "dep_id=#{depId}," +
            "</if>" +
            "</trim>" +
            "where user_id=#{userId} and year=#{time}" +
            "</script>")
    Integer updateAim(int quarter1,int quarter2,int quarter3,
                  int quarter4,int yr,String updateTime,String depId,String userId,String time);


    /**
     * 按年份查询信息
     * @param time
     * @return
     */
    @Select("SELECT sys_user.user_id as userId ," +
            "sys_user.true_name as trueName," +
            "crm_user_aim.quarter1,crm_user_aim.dep_id as depId," +
            "sys_department.dep_name as depName,\n" +
            "crm_user_aim.quarter2 ,crm_user_aim.quarter3," +
            "crm_user_aim.quarter4 ,crm_user_aim.yr,crm_user_aim.year from sys_user \n" +
            "left join (select * from  crm_user_aim where year=#{time}) crm_user_aim\n" +
            "on sys_user.user_id =crm_user_aim.user_id\n" +
            "left join sys_department  on crm_user_aim.dep_id  =sys_department.id where sys_user.role_id='001' and sys_user.disabled=0 ORDER BY sys_user.dep_id \n" )
    List<Aim> yearAim(String time);

    /**
     * 获取所有信息
     * @return
     */
    @Select("SELECT crm_user_aim.create_time as createTime, sys_user.user_id as userId,sys_user.true_name as trueName,crm_user_aim.quarter1,crm_user_aim.dep_id as depId,sys_department.dep_name as depName,\n" +
            "crm_user_aim.quarter2 ,crm_user_aim.quarter3,crm_user_aim.quarter4 ,crm_user_aim .yr from sys_user \n" +
            "left join crm_user_aim\n" +
            "on sys_user.user_id =crm_user_aim.user_id\n" +
            "left join sys_department  on crm_user_aim.dep_id  =sys_department.id where sys_user.role_id='001' and sys_user.disabled=0 order BY crm_user_aim.create_time , sys_user.user_id ")
    List<Aim> allAim();

    /**
     * 获取最小时间时间
     * @return
     */
    @Select("SELECT MIN(create_time)  as minTime " +
            "from crm_user_aim ")
    String minTime();

    /**
     * 获取最大时间
     * @return
     */
    @Select("SELECT Max(create_time) as maxTime " +
            "from crm_user_aim ")
    String maxTime();

    /**
     * 获取所有部门信息
     * @return
     */
    @Select("SELECT sys_user.user_id as userId,sys_department.dep_name as depName,sys_user.dep_id as depId," +
            "sys_user.true_name as trueName from" +
            " sys_user left join sys_department on " +
            "sys_user.dep_id=sys_department.id")
    List<Aim> allName();

    /**
     * 查询部门号和姓名
     */
    @Select("SELECT sys_user.dep_id as depId,\n" +
            "            sys_user.true_name as trueName from\n" +
            "             sys_user left join sys_department on \n" +
            "            sys_user.dep_id=sys_department.id where sys_user.user_id =#{userId}")
    SysUser user(String userId);

    /**
     * 录入客户数量
     * @param qua
     * @param yr
     * @return
     */
    @Select(" SELECT sys_user.user_id as userId,sys_user.true_name as trueName,COUNT(distinct(crm_partner.telephone)) as quantity1,COUNT(*) as partner1 from crm_partner left join sys_user on crm_partner.create_user_id =sys_user.user_id inner join sys_department on sys_user.dep_id =sys_department.id where\n" +
            "sys_user.role_id='001' and   QUARTER(crm_partner.create_time ) = #{qua} and YEAR(crm_partner.create_time)=#{yr} and  sys_user.disabled=0 and crm_partner .disabled=0\n" +
            "GROUP by user_id\n" +
            "order by dep_id,user_id")
    List<AimExcel> quarter(int qua, String yr);

    @Select("SELECT sys_user.user_id as userId,sys_user.true_name as trueName ,sys_user.dep_id as depId \n" +
            "                        from  sys_user  left join crm_partner  on sys_user.user_id=crm_partner.create_user_id   where\n" +
            "                       sys_user.role_id='001' \n" +
            "                        and  sys_user.disabled=0 \n" +
            "                       GROUP by user_id order by dep_id,user_id")
    List<AimExcel> allInfo();
}
